package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;



import crm.dbcoon.DBConnect;

import crm.model.all_sum;
import crm.util.Pagination;

public class AllSumControl {
	public static Pagination pageInstance = null;
	/**
	 * 获取最新插入的id
	 * @return
	 */
	public static int getRushAllSumId() {
		DBConnect dbc = new DBConnect();
		String sql = "select max(id) from all_sum";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				System.out.println(id+"mmm");
				return id;
			}
			return 0;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return -1;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 
	 * @return
	 */
	public static boolean addAllSum(all_sum allSum) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into all_sum(start_time,end_time,emp_id) values(?,?,?)";
		dbc.prepareStatement(sql);
		try {
			int index = 1;
			dbc.setString(index++, allSum.getStart_time());
			dbc.setString(index++, allSum.getEnd_time());
			dbc.setInt(index++, allSum.getEmp_id());
			dbc.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	/**
	 * 
	 * @return
	 */
	public static Collection<all_sum> getAllSums(int position,int employee_id) {
		Collection<all_sum> as = new ArrayList<all_sum>();
		DBConnect dbc = new DBConnect();
		if(position != 1){
		String sql1 = "select * from all_sum";
		dbc.prepareStatement(sql1);
		}else if(position == 1){
			String sql2 = "select * from all_sum where emp_id='"+employee_id+"' order by id desc";
			dbc.prepareStatement(sql2);
		}
		ResultSet rs = null;
		try {
			rs=dbc.executeQuery();
			while (rs.next()) {
				all_sum allSum = new all_sum();
				allSum.setId(rs.getInt("id"));
				allSum.setStart_time(rs.getString("start_time"));
				allSum.setEnd_time(rs.getString("end_time"));
				allSum.setEmp_id(rs.getInt("emp_id"));
				as.add(allSum);
			}
			return as;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		finally{
			dbc.close();
		}
	}
	
	public static Collection<Object> searchSum(String start_time,String end_time,String create_person,int pages,int rowPerPage) {
		if(start_time==null||start_time.equals(""))
			start_time = "%";
		if(end_time==null||end_time.equals(""))
			end_time = "%";
		if(create_person==null||create_person.equals(""))
			create_person = "%";
		String sql = "select all_sum.* from all_sum,info_employees where all_sum.emp_id = info_employees.id and info_employees.name like '%"+create_person+"%'" +
				"and all_sum.start_time like '%"+start_time+"%'" +
						"and all_sum.end_time like '%"+end_time+"%'";
		System.out.println(sql);
		pageInstance = new AllSumControlPage(sql, rowPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}
}
class AllSumControlPage extends Pagination{
	public AllSumControlPage(String sql,int rowsPerPage){
		super(sql, rowsPerPage, 1);
	}
	protected Collection<all_sum> packResultSet(ResultSet rs) {
		Collection<all_sum> as = new ArrayList<all_sum>();
		try{
			while(rs.next()){
				 all_sum allSum = new all_sum();
				 allSum.setId(rs.getInt("id"));
				 allSum.setStart_time(rs.getString("start_time"));
				 allSum.setEnd_time(rs.getString("end_time"));
				 allSum.setEmp_id(rs.getInt("emp_id"));
				 as.add(allSum);
				 allSum = null; 
			}
		}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<all_sum>出错");
		}
		return as;
	}
}

